using System;
using System.Collections.Generic;
using RedGate.SQLPackager.Engine;
using RedGate.SQLCompare.Engine;
using RedGate.SQL.Shared;
using RedGate.SQLDataCompare.Engine;
using System.Xml;
using System.Text;
using System.IO;
using Stratman.Utilities;
using System.Diagnostics;

namespace Stratman.DesktopProcess.DatabaseSynchronizer
{
    /// <summary>
    /// Takes a pre-existing database and generates schema and data scripts for it.
    /// </summary>
    public class DatabaseToFilesProject : SynchronizerProject
    {
        /// <summary>
        /// Root path under which the scripts will be saved.
        /// </summary>
        protected string targetDirectoryPath = "";
        /// <summary>
        /// The provider object, if any, that should be used to interact with the source code
        /// repository.
        /// </summary>
        protected ISccProvider sccProvider = null;

        /// <summary>
        /// Load the synchronization settings from the project file.
        /// </summary>
        /// <param name="projectFilePath">
        /// File from which we are to load settings.
        /// </param>
        /// <param name="commandLineArguments">
        /// Command line arguments that were specified.
        /// </param>
        protected override void LoadFromFile(string projectFilePath, string[] commandLineArguments)
        {
            // Instantiate the member variables from the command line arguments
            foreach (string argument in commandLineArguments)
            {
                if (argument.ToLower().StartsWith("/scc:"))
                {
                    switch (argument.ToLower().Substring(5))
                    {
                        case "svn":
                            sccProvider = new SvnSccProvider();
                            break;

                        default:
                            throw new ArgumentException("The value of the /scc parameter must be SVN.");
                    }
                }
            }

            XmlDocument project = new XmlDocument();
            project.Load(projectFilePath);

            // Get the target directory and the standard synchronization options
            targetDirectoryPath = XmlUtilities.GetSafeXmlValue(project, "/SynchronizerProject/Target/Directory", "");
            synchronizationOptions.Load(projectFilePath);
        }

        /// <summary>
        /// Generates the schema and data scripts for the given database.
        /// </summary>
        public override void Execute()
        {
            Dictionary<string, List<string>> scriptedObjects = new Dictionary<string, List<string>>();

            Database sourceDatabaseInstance = new Database();
            sourceDatabaseInstance.Status = new StatusEventHandler(StatusUpdate);

            // Only use source control if we were directed to do so on the command line and the
            // base target directory is under source control
            bool useSourceControl = (sccProvider != null && sccProvider.IsUnderSourceControl(targetDirectoryPath));

            try
            {
                // Register the database for schema export
                TraceInformation("Registering source database ({0}.{1}) for schema export", synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase);
                sourceDatabaseInstance.Register(CreateConnectionProperties(synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase, synchronizationOptions.SourceUserName, synchronizationOptions.SourcePassword), Options.Default);

                // Get the list of objects in the source database
                Differences differences = sourceDatabaseInstance.CompareWith(null, Options.Default);
                ObjectType previousObjectType = ObjectType.None;

                foreach (Difference difference in differences)
                {
                    // Only generate scripts for the objects that the synchronization options
                    // indicate that we should process
                    if (!ShouldProcessDifference(difference, synchronizationOptions))
                        continue;

                    if (difference.DatabaseObjectType != previousObjectType)
                    {
                        TraceInformation("Exporting {0}", StringUtilities.DePascalCase(StringUtilities.Pluralize(difference.DatabaseObjectType.ToString())).ToLower());
                        previousObjectType = difference.DatabaseObjectType;
                    }

                    Work work = new Work();

                    try
                    {
                        // Write the T-SQL for this individual object to a file
                        TraceVerbose("Exporting the {0} {1}", StringUtilities.DePascalCase(difference.DatabaseObjectType.ToString()).ToLower(), GetFileName(difference.Name, false));
                        SchemaObject databaseObject = difference.ObjectIn1 as SchemaObject;
                        List<Permission> permissions = new List<Permission>();

                        work.BuildFromDifferences(differences, difference, Options.Default | Options.IgnoreKeys | Options.IgnoreTriggers | Options.NoSQLPlumbing | Options.IgnorePermissions | Options.IgnoreUsers, true);

                        if (databaseObject != null && databaseObject.Permissions.Count > 0)
                        {
                            foreach (Permission permission in databaseObject.Permissions)
                            {
                                if ((sourceDatabaseInstance.Users[permission.UserName] != null && ShouldProcessDifference(ObjectType.User, permission.UserName, synchronizationOptions)) || (sourceDatabaseInstance.Roles[permission.UserName] != null && ShouldProcessDifference(ObjectType.Role, permission.UserName, synchronizationOptions)))
                                    permissions.Add(permission);
                            }
                        }

                        WriteExecutionBlock(targetDirectoryPath + "\\" + StringUtilities.DePascalCase(StringUtilities.Pluralize(difference.DatabaseObjectType.ToString())) + "\\" + GetFileName(difference.Name), work.ExecutionBlock, difference.ObjectIn1, permissions, useSourceControl);
                    }

                    finally
                    {
                        // Clean up the objects
                        if (work.ExecutionBlock != null)
                            work.ExecutionBlock.Dispose();
                    }

                    // Add the object to the list of those that we've written to disk
                    if (!scriptedObjects.ContainsKey(difference.DatabaseObjectType.ToString("G")))
                        scriptedObjects.Add(difference.DatabaseObjectType.ToString("G"), new List<string>());

                    scriptedObjects[difference.DatabaseObjectType.ToString("G")].Add(GetFileName(difference.Name));
                }

                TraceInformation("Exporting foreign keys and triggers");

                // Write out foreign keys and triggers in a separate operation:  there is no way
                // to separate foreign keys and tables in Red Gate, and the 
                // Options.SeparateTriggers option doesn't work correctly
                foreach (Table table in sourceDatabaseInstance.Tables)
                {
                    if (table.ForeignKeys.Count > 0 && ShouldProcessDifference(ObjectType.Table, table.FullyQualifiedName, synchronizationOptions))
                    {
                        TraceVerbose("Exporting the foreign keys for {0}", GetFileName(table.FullyQualifiedName, false));
                        WriteForeignKeys(table, scriptedObjects, useSourceControl);
                    }

                    if (table.Triggers.Count > 0)
                    {
                        TraceVerbose("Exporting the triggers for {0}", GetFileName(table.FullyQualifiedName, false));
                        WriteTriggers(table, scriptedObjects, synchronizationOptions, useSourceControl);
                    }
                }

                // Generate data-insertion scripts for any static tables
                if (synchronizationOptions.StaticTables.Count > 0)
                {
                    // Register the database for data export
                    TraceInformation("Registering source database ({0}.{1}) for data export", synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase);
                    sourceDatabaseInstance.RegisterForDataCompare(CreateConnectionProperties(synchronizationOptions.SourceServerName, synchronizationOptions.SourceDatabase, synchronizationOptions.SourceUserName, synchronizationOptions.SourcePassword));

                    TableMappings mappings = new TableMappings();
                    EngineDataCompareOptions options = new EngineDataCompareOptions();

                    // Get the list of tables from the database
                    options.MappingOptions |= MappingOptions.MissingFrom2AsInclude;
                    options.SqlOptions = SqlOptions.OutputComments | SqlOptions.UseTransactions;
                    mappings.Options = options;
                    mappings.CreateMappings(sourceDatabaseInstance.Tables, null);

                    foreach (TableMapping mapping in mappings)
                        mapping.Include = false;

                    ComparisonSession dataComparisonSession = null;
                    ExecutionBlock executionBlock = null;
                    SqlProvider provider = null;

                    TraceInformation("Exporting table data");

                    try
                    {
                        foreach (TableMapping mapping in mappings)
                        {
                            // Only generate data-insertion scripts for tables that are marked in
                            // the synchronization options as static tables
                            if (synchronizationOptions.StaticTables.Contains(mapping.Obj1.FullyQualifiedName))
                            {
                                TraceVerbose("Exporting the data for the table {0}", GetFileName(mapping.Obj1.FullyQualifiedName, false));

                                dataComparisonSession = new ComparisonSession();
                                provider = new SqlProvider();

                                dataComparisonSession.Options = options;
                                provider.Options = options;

                                mapping.Include = true;
                                dataComparisonSession.CompareDatabases(sourceDatabaseInstance, null, mappings);
                                executionBlock = provider.GetMigrationSQL(dataComparisonSession, true);

                                // Write the data T-SQL to a file
                                WriteExecutionBlock(targetDirectoryPath + "\\Static Tables\\" + GetFileName(mapping.Obj1.FullyQualifiedName), executionBlock, mapping.Obj1, null, useSourceControl);
                                executionBlock.Dispose();
                                dataComparisonSession.Dispose();

                                mapping.Include = false;

                                if (!scriptedObjects.ContainsKey("StaticTable"))
                                    scriptedObjects.Add("StaticTable", new List<string>());

                                scriptedObjects["StaticTable"].Add(GetFileName(mapping.Obj1.FullyQualifiedName));
                            }
                        }
                    }

                    finally
                    {
                        // Cleanup the various objects
                        if (dataComparisonSession != null)
                            dataComparisonSession.Dispose();

                        if (executionBlock != null)
                            executionBlock.Dispose();
                    }
                }
            }

            finally
            {
                sourceDatabaseInstance.Dispose();
            }

            if (!noChanges)
            {
                // Remove object scripts that no longer apply to the database
                foreach (string directoryPath in Directory.GetDirectories(targetDirectoryPath))
                {
                    DirectoryInfo directory = new DirectoryInfo(directoryPath);

                    // If we're looking at an SCC admin directory, skip it
                    if (sccProvider != null && sccProvider.AdminDirectories.Contains(directory.Name))
                        continue;

                    if (Enum.IsDefined(typeof(ObjectType), StringUtilities.PascalCase(StringUtilities.Singularize(directory.Name))) || directory.Name == "Foreign Keys" || directory.Name == "Static Tables")
                    {
                        string key = StringUtilities.PascalCase(StringUtilities.Singularize(directory.Name));

                        foreach (FileInfo file in directory.GetFiles())
                        {
                            if (!scriptedObjects.ContainsKey(key) || !scriptedObjects[key].Contains(file.Name))
                                DeleteFile(file.FullName);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Script out the triggers for a table to individual files.
        /// </summary>
        /// <param name="table">
        /// Table whose triggers we are to script.
        /// </param>
        /// <param name="scriptedObjects">
        /// A list of objects that have been scripted out as part of this synchronization run.
        /// </param>
        /// <param name="synchronizationOptions">
        /// Options for this synchronization operation
        /// </param>
        /// <param name="useSourceControl">
        /// Flag indicating whether or not we are making use of source control during this run.
        /// </param>
        protected void WriteTriggers(Table table, Dictionary<string, List<string>> scriptedObjects, SynchronizationOptions synchronizationOptions, bool useSourceControl)
        {
            if (table.Triggers.Count == 0)
                return;

            foreach (Trigger trigger in table.Triggers)
            {
                // Only generate scripts for triggers that the synchronization options indicate
                // that we should process
                if (!ShouldProcessDifference(ObjectType.Trigger, trigger.FullyQualifiedName, synchronizationOptions))
                    continue;

                // Create the T-SQL for the trigger
                StringBuilder sql = new StringBuilder("SET NUMERIC_ROUNDABORT OFF\r\nGO\r\nSET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON\r\nGO\r\nSET XACT_ABORT ON\r\nGO\r\nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE\r\nGO\r\n");

                sql.AppendFormat("PRINT N'Creating trigger {0} on {1}'\r\nGO\r\n\r\n", trigger.FullyQualifiedName, table.FullyQualifiedName);
                sql.Append(trigger.Text.Trim() + "\r\n");
                sql.Append("GO\r\n");

                // Write the T-SQL to a file
                FileInfo file = new FileInfo(targetDirectoryPath + "\\Triggers\\" + GetFileName(trigger.FullyQualifiedName));
                Directory.CreateDirectory(file.DirectoryName);

                bool fileUnderSourceControl = (useSourceControl ? sccProvider.IsUnderSourceControl(file.FullName) : false);
                string tempFileName = (fileUnderSourceControl ? Path.GetTempFileName() : "");
                StreamWriter fileWriter = new StreamWriter(new FileStream((fileUnderSourceControl ? tempFileName : file.FullName), FileMode.Create));

                fileWriter.Write(sql.ToString());
                fileWriter.Close();

                // If the file is under source control, take the temporary file, compare it to the
                // main file, and, if they differ, copy the temp file to the main file
                if (fileUnderSourceControl)
                    MergeFiles(tempFileName, file.FullName);

                // Otherwise, if we're making use of source control, add the file to the repository
                else if (useSourceControl)
                {
                    if (!sccProvider.IsUnderSourceControl(file.Directory.FullName))
                        sccProvider.Add(file.Directory.FullName);

                    sccProvider.Add(file.FullName);
                }

                // Add the object to the list of scripted objects
                if (!scriptedObjects.ContainsKey("Trigger"))
                    scriptedObjects.Add("Trigger", new List<string>());

                scriptedObjects["Trigger"].Add(GetFileName(trigger.FullyQualifiedName));
            }
        }

        /// <summary>
        /// Script out the foreign keys for a table into an individual file.
        /// </summary>
        /// <param name="table">
        /// Table whose foreign keys we are to script.
        /// </param>
        /// <param name="scriptedObjects">
        /// A list of objects that have been scripted out as part of this synchronization run.
        /// </param>
        /// <param name="useSourceControl">
        /// Flag indicating whether or not we are making use of source control during this run.
        /// </param>
        protected void WriteForeignKeys(Table table, Dictionary<string, List<string>> scriptedObjects, bool useSourceControl)
        {
            if (table.ForeignKeys.Count == 0 || noChanges)
                return;

            // Create the header T-SQL
            StringBuilder sql = new StringBuilder("SET NUMERIC_ROUNDABORT OFF\r\nGO\r\nSET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON\r\nGO\r\nSET XACT_ABORT ON\r\nGO\r\nSET TRANSACTION ISOLATION LEVEL SERIALIZABLE\r\nGO\r\n");

            sql.AppendFormat("PRINT N'Adding foreign keys to {0}'\r\nGO\r\n", table.FullyQualifiedName);
            sql.AppendFormat("ALTER TABLE {0} ADD\r\n", table.FullyQualifiedName);

            // Build the T-SQL necessary for each foreign key
            for (int i = 0; i < table.ForeignKeys.Count; i++)
            {
                ForeignKey foreignKey = table.ForeignKeys[i];

                StringBuilder localColumns = new StringBuilder();
                StringBuilder referencedColumns = new StringBuilder();

                foreach (Field field in foreignKey.Fields)
                    localColumns.AppendFormat("[{0}], ", field.FullyQualifiedName);

                foreach (Field field in foreignKey.ReferencedFields)
                    referencedColumns.AppendFormat("[{0}], ", field.FullyQualifiedName);

                string localColumnsList = localColumns.ToString();
                localColumnsList = localColumnsList.Substring(0, localColumnsList.Length - 2);

                string referencedColumnsList = referencedColumns.ToString();
                referencedColumnsList = referencedColumnsList.Substring(0, referencedColumnsList.Length - 2);

                sql.AppendFormat("CONSTRAINT [{0}] FOREIGN KEY ({1}) REFERENCES {2} ({3})", foreignKey.Name, localColumnsList, foreignKey.ReferencedTable.FullyQualifiedName, referencedColumnsList);

                if (i < table.ForeignKeys.Count - 1)
                    sql.AppendLine(",");
            }

            // Add the footer T-SQL
            sql.Append("\r\nGO\r\n");

            // Write it all to a file
            FileInfo file = new FileInfo(targetDirectoryPath + "\\Foreign Keys\\" + GetFileName(table.FullyQualifiedName));
            Directory.CreateDirectory(file.DirectoryName);

            bool fileUnderSourceControl = (useSourceControl ? sccProvider.IsUnderSourceControl(file.FullName) : false);
            string tempFileName = (fileUnderSourceControl ? Path.GetTempFileName() : "");
            StreamWriter fileWriter = new StreamWriter(new FileStream((fileUnderSourceControl ? tempFileName : file.FullName), FileMode.Create));

            fileWriter.Write(sql.ToString());
            fileWriter.Close();

            // If the file is under source control, take the temporary file, compare it to the
            // main file, and, if they differ, copy the temp file to the main file
            if (fileUnderSourceControl)
                MergeFiles(tempFileName, file.FullName);

            // Otherwise, if we're making use of source control, add the file to the repository
            else if (useSourceControl)
            {
                if (!sccProvider.IsUnderSourceControl(file.Directory.FullName))
                    sccProvider.Add(file.Directory.FullName);

                sccProvider.Add(file.FullName);
            }

            // Add the object to the list of scripted objects
            if (!scriptedObjects.ContainsKey("ForeignKey"))
                scriptedObjects.Add("ForeignKey", new List<string>());

            scriptedObjects["ForeignKey"].Add(file.Name);
        }

        /// <summary>
        /// Write the T-SQL for a generic execution block (for a table, stored procedure, view, 
        /// etc.) to a file.
        /// </summary>
        /// <param name="filePath">
        /// File to which we write the script.
        /// </param>
        /// <param name="executionBlock">
        /// Execution block that we are to script.
        /// </param>
        /// <param name="databaseObject">
        /// Object to which this execution block corresponds.
        /// </param>
        /// <param name="permissions">
        /// List of permissions associated with the object.
        /// </param>
        /// <param name="useSourceControl">
        /// Flag indicating whether or not we are to make use of source control.
        /// </param>
        protected void WriteExecutionBlock(string filePath, ExecutionBlock executionBlock, IDatabaseObject databaseObject, List<Permission> permissions, bool useSourceControl)
        {
            if (noChanges)
                return;

            // Write the T-SQL to a file
            FileInfo file = new FileInfo(filePath);
            Directory.CreateDirectory(file.DirectoryName);

            bool fileUnderSourceControl = (useSourceControl ? sccProvider.IsUnderSourceControl(filePath) : false);
            string tempFileName = (fileUnderSourceControl ? Path.GetTempFileName() : "");
            StreamWriter fileWriter = new StreamWriter(new FileStream((fileUnderSourceControl ? tempFileName : filePath), FileMode.Create));

            // We use special processing on stored procedures and functions; due to a bug in the
            // SQL management objects, they have a bad habit of spitting out extra whitespace at
            // the end of the scripts for both of those database types even if the user only
            // scripted and ran the script for the object
            if (databaseObject.ObjectType == ObjectType.StoredProcedure || databaseObject.ObjectType == ObjectType.Function)
            {
                for (long i = 0; i < executionBlock.BatchCount; i++)
                {
                    string batch = executionBlock.GetBatch(i).Contents;

                    if (batch.Trim().StartsWith("CREATE "))
                        batch = batch.Trim() + Environment.NewLine;

                    fileWriter.Write(batch);
                }
            }

            else
                fileWriter.Write(executionBlock.ToString());

            // Append any permissions for the object to the end of the file
            if (permissions != null && permissions.Count > 0)
            {
                foreach (Permission permission in permissions)
                {
                    fileWriter.WriteLine("{0} {1} ON {2} TO {3}", permission.Type.ToString("G").ToUpper(), permission.Action.ToString("G").ToUpper(), databaseObject.FullyQualifiedName, GetFullyQualifiedName(permission.UserName));
                    fileWriter.WriteLine("GO");
                }
            }

            // Add members to roles based on whether we are processing the member or not
            if (databaseObject.ObjectType == ObjectType.Role)
            {
                Role role = (Role)databaseObject;

                foreach (IDatabaseObject roleMember in role.Members)
                {
                    if (ShouldProcessDifference(roleMember.ObjectType, roleMember.Name, synchronizationOptions))
                    {
                        fileWriter.WriteLine("sp_addrolemember N'{0}', N'{1}'", role.Name, roleMember.Name);
                        fileWriter.WriteLine("GO");
                    }
                }
            }

            fileWriter.Close();

            // If the file is under source control, take the temporary file, compare it to the
            // main file, and, if they differ, copy the temp file to the main file
            if (fileUnderSourceControl)
                MergeFiles(tempFileName, filePath);

            // Otherwise, if we're making use of source control, add the file to the repository
            else if (useSourceControl)
            {
                if (!sccProvider.IsUnderSourceControl(file.Directory.FullName))
                    sccProvider.Add(file.Directory.FullName);

                sccProvider.Add(filePath);
            }
        }

        /// <summary>
        /// Compares two files by generating an MD5 hash of each of them and, if they're different,
        /// copies the source file to the destination file.  If they are the same, then the source
        /// file is deleted.
        /// </summary>
        /// <param name="sourceFileName">
        /// The path to the source file that will be copied if the two files differ.
        /// </param>
        /// <param name="destinationFileName">
        /// The path to the destination file that will be overwritten if the two files differ.
        /// </param>
        protected void MergeFiles(string sourceFileName, string destinationFileName)
        {
            FileStream sourceStream = new FileStream(sourceFileName, FileMode.Open);
            FileStream destinationStream = new FileStream(destinationFileName, FileMode.Open);

            // Compare the contents of the two file streams by way of their hashes
            if (Convert.ToBase64String(EncryptionUtilities.ComputeMD5CheckSum(sourceStream)) != Convert.ToBase64String(EncryptionUtilities.ComputeMD5CheckSum(destinationStream)))
            {
                // Check out the file from source control if necessary
                if (!sccProvider.IsCheckedOut(destinationFileName))
                    sccProvider.CheckOut(destinationFileName);

                destinationStream.Close();
                sourceStream.Close();

                // Copy the source file to the destination
                File.Delete(destinationFileName);
                File.Move(sourceFileName, destinationFileName);
            }

            // If the two files are identical, delete the source file
            else
            {
                sourceStream.Close();
                destinationStream.Close();

                File.Delete(sourceFileName);
            }
        }

        /// <summary>
        /// Deletes a file from the filesystem and, if applicable, the source control repository.
        /// </summary>
        /// <param name="filePath">
        /// Path to the file that we are to delete.
        /// </param>
        protected void DeleteFile(string filePath)
        {
            // If we're not using source control or the file is not under source control, just 
            // delete it
            if (sccProvider == null || !sccProvider.IsUnderSourceControl(filePath))
                File.Delete(filePath);

            // Otherwise, invoke the Delete() method in the SCC provider object
            else
                sccProvider.Delete(filePath);
        }
    }
}
